<?php
    $nombreTipo = ($tipo == 1) ? "Consolidado" : "Detallado";
    switch ($tipo_reporte) {
        case 2:
            $nombre = "Ventas_por_Cliente_{$nombreTipo}_".date("d_m_Y");
            $titulo = "Ventas por Cliente";
            break;
        case 3:
            $nombre = "Ventas_por_Material_{$nombreTipo}_".date("d_m_Y");
            $titulo = "Ventas por Material";
        case 4:
            $nombre = "Ventas_por_Cliente_Material_{$nombreTipo}_".date("d_m_Y");
            $titulo = "Ventas por Cliente - Material";
        break;
    }

    $tit[] = array('#','STR','C');
    $tit[] = array('AGENCIA','STR','C');
    $tit[] = array('CEDULA/RIF','STR','C');
    $tit[] = array('CODIGO SAP','STR','C');
    $tit[] = array('RAZON SOCIAL','STR','C');
    $tit[] = array('FACTURA','STR','C');
    $tit[] = array('ESTATUS','STR','C');
    $tit[] = array('TIPO','STR','C');
    $tit[] = array('FECHA','STR','C');
    $tit[] = array('CODIGO ARTICULO','STR','C');
    $tit[] = array('DESCRIPCION','STR','C');
    $tit[] = array('CANTIDAD','STR','C');
    $tit[] = array('PRECIO','STR','C');
    $tit[] = array('TOTAL','STR','C');

    $this->PhpExcel->Excel();
    $ext = ($ext == 1) ? 'xlsx' : 'ods';
    $vacio = '-';
    $arrStyleTitle = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,
                                'startcolor' => array('argb' => '81DAF5')),
                            'font' => array('bold' => true, 'color' => array('rgb' => PHPExcel_Style_Color::COLOR_WHITE)),
                            'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));

    $arrStyleTotales = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,
                                'startcolor' => array('argb' => '81DAF5')),
                            'font' => array('bold' => true, 'color' => array('rgb' => PHPExcel_Style_Color::COLOR_WHITE)));

    $arrStyleTitleuno = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('argb' => 'EFFBFB')));
    $arrborderTitleuno = array('style' => PHPExcel_Style_Border::BORDER_DASHDOT,'color' => array('rgb' => '000000'));

    if($tipo == 1) {
        $this->PhpExcel->titulo_reporte("SIGESP UTILITARIO", $titulo);
        $this->PhpExcel->titulo_hoja($titulo);	

        $this->PhpExcel->hoja->getDefaultStyle()->applyFromArray(array('font' => array('name' => 'arial','size' => 10)));
        $this->PhpExcel->hoja->getStyle('A1')->applyFromArray(array('font' => array('bold' => true, 'size' => 12, 'color' => array('argb' => '8B0000'))));
        $this->PhpExcel->hoja->getStyle('A2')->applyFromArray(array('font' => array('bold' => true, 'size' => 11)));
        $this->PhpExcel->hoja->getStyle('A3')->applyFromArray(array('font' => array('bold' => false,'size' => 10)));
        $this->PhpExcel->hoja->getStyle('A4')->applyFromArray(array('font' => array('bold' => false,'size' => 10)));

        $this->PhpExcel->configura_celda('A',5); //#
        $this->PhpExcel->configura_celda('B',30); //AGENCIA
        $this->PhpExcel->configura_celda('C',15); //CEDULA/RIF
        $this->PhpExcel->configura_celda('D',15); //CODIGO SAP
        $this->PhpExcel->configura_celda('E',40); //RAZON SOCIAL
        $this->PhpExcel->configura_celda('F',28); //FACTURA
        $this->PhpExcel->configura_celda('G',15); //ESTATUS
        $this->PhpExcel->configura_celda('H',10); //TIPO
        $this->PhpExcel->configura_celda('I',10); //FECHA
        $this->PhpExcel->configura_celda('J',20); //CODIGO ARTICULO
        $this->PhpExcel->configura_celda('K',55); //DESCRIPCION
        $this->PhpExcel->configura_celda('L',10); //CANTIDAD
        $this->PhpExcel->configura_celda('M',15); //PRECIO
        $this->PhpExcel->configura_celda('N',15); //TOTAL

        $index_title = 6;
        $index_detail = 0;
        $j = 0;

        foreach($tit as $columnTitle) {
            $this->PhpExcel->valor_celda($j, $index_title, $columnTitle);
            $oColumn = $this->PhpExcel->obtener_columna($j, $index_title);
            $oRow = $this->PhpExcel->obtener_fila($j, $index_title);
            $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->applyFromArray($arrStyleTitle);
            $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->getBorders()->getAllBorders()->applyFromArray( $arrborderTitleuno);
            $j++;
        }

        $fila = 7;
        $i = 0;
        $j = 1;
        $documento = '';
        $subtotal = $total = 0;
        foreach($data as $indice => $valor) {
            $valores = array();
            $noRepetir = ($documento != $valor['documento']) ? true : false;
            $documento = $valor['documento'];
            if ($noRepetir)
                $i++;
            $valores[] = array(($noRepetir) ? $i : $vacio,'STR', 'C');
            $valores[] = array($valor['agencia'] = ($noRepetir) ? $valor['agencia'] : $vacio,'STR', ($noRepetir) ? 'L' : 'C');
            $valores[] = array($valor['cedcli'] = ($noRepetir) ? $valor['cedcli'] : $vacio,'STR','C');
            $valores[] = array($valor['codclisap'] = ($noRepetir) ? $valor['codclisap'] : $vacio,'STR','C');
            $valores[] = array($valor['razcli'] = ($noRepetir) ? $this->Interfaz->limpiar_campo($valor['razcli']) : $vacio,'STR', ($noRepetir) ? 'L' : 'C');
            $valores[] = array($valor['documento'] = ($noRepetir) ? $valor['documento'] : $vacio,'STR', ($noRepetir) ? 'L' : 'C');
            $valores[] = array($valor['estatus'] = ($noRepetir) ? $valor['estatus'] : $vacio,'STR', ($noRepetir) ? 'L' : 'C');
            $valores[] = array($valor['tipo'] = ($noRepetir) ? $valor['tipo'] : $vacio,'STR', ($noRepetir) ? 'L' : 'C');
            $valores[] = array($valor['fecemi'] = ($noRepetir) ? $this->Interfaz->fecha($valor['fecemi']) : $vacio,'STR','C');
            $valores[] = array($valor['codart'],'STR','L');
            $valores[] = array($valor['denart'],'STR','L');
            $valores[] = array($valor['canpro'],'N','C');
            $valores[] = array($valor['prepro'],'N','R');
            $subtotal = ($valor['canpro'] * $valor['prepro']);
            $valores[] = array($subtotal,'N','R');
            $total += $subtotal;

            $columna = 0;
            foreach($valores as $valor2) {
                $this->PhpExcel->valor_celda($columna, $fila,  $valor2);
                $oColumn = $this->PhpExcel->obtener_columna($columna, $fila);
                $oRow = $this->PhpExcel->obtener_fila($columna, $fila);

                if($i%2 == 0)
                    $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->applyFromArray($arrStyleTitleuno);

                    $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->getBorders()->getAllBorders()->applyFromArray($arrborderTitleuno);
                    $columna++;
            }
            $fila++;
            $j++;
        }
        $totales = array();
        $totales[] = array('Total:', 'STR', 'C');
        $totales[] = array($total, 'N', 'R');

        $columna = 12;
        foreach($totales as $total) {
            $this->PhpExcel->valor_celda($columna, $fila, $total);
            $oColumn = $this->PhpExcel->obtener_columna($columna, $fila);
            $oRow = $this->PhpExcel->obtener_fila($columna, $fila);
            $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->applyFromArray($arrStyleTotales);
            $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->getBorders()->getAllBorders()->applyFromArray( $arrborderTitleuno);
            $columna++;
        }
    } else {
        $k = 0;
        foreach($tienda_buscar as $indice3 => $valor3) {
            if($k > 0)
                $this->PhpExcel->xls->createSheet();
            $this->PhpExcel->xls->setActiveSheetIndex($k);
            $oac=$this->PhpExcel->xls->getActiveSheet();
            $oac->setTitle($valor3);
            $oac->getDefaultStyle()->applyFromArray(array('font' => array('name' => 'arial','size' => 10)));
            $oac->getStyle('A1')->applyFromArray(array('font' => array('bold' => true, 'size' => 12, 'color' => array('argb' => '8B0000'))));
            $oac->getStyle('A2')->applyFromArray(array('font' => array('bold' => true, 'size' => 11)));
            $oac->getStyle('A3')->applyFromArray(array('font' => array('bold' => false,'size' => 10)));
            $oac->getStyle('A4')->applyFromArray(array('font' => array('bold' => false,'size' => 10)));
            $oac->setCellValue('A1', "AGROPATRIA - SIGESP UTILITARIO");
            $oac->setCellValue('A2', $this->Interfaz->mayus($titulo).' TIENDA '.$this->Interfaz->mayus($tienda[$valor3]) );
            $oac->setCellValue('A3', "Fecha: ".date("d-m-Y"));
            $oac->setCellValue('A4', "Hora: ".date("h:i"));

            $oac->getColumnDimension('A')->setWidth(5); //#
            $oac->getColumnDimension('B')->setWidth(30); //AGENCIA
            $oac->getColumnDimension('C')->setWidth(15); //CEDULA/RIF
            $oac->getColumnDimension('D')->setWidth(15); //CODIGO SAP
            $oac->getColumnDimension('E')->setWidth(40); //RAZON SOCIAL
            $oac->getColumnDimension('F')->setWidth(28); //FACTURA
            $oac->getColumnDimension('G')->setWidth(15); //ESTATUS
            $oac->getColumnDimension('H')->setWidth(10); //TIPO
            $oac->getColumnDimension('I')->setWidth(10); //FECHA
            $oac->getColumnDimension('J')->setWidth(20); //CODIGO ARTICULO
            $oac->getColumnDimension('K')->setWidth(55); //DESCRIPCION
            $oac->getColumnDimension('L')->setWidth(10); //CANTIDAD
            $oac->getColumnDimension('M')->setWidth(15); //PRECIO
            $oac->getColumnDimension('N')->setWidth(15); //TOTAL

            $index_title = 6;
            $index_detail = 0;
            $j = 0;

            foreach($tit as $columnTitle) {
                $oac->getCellByColumnAndRow($j,$index_title)->setValueExplicit($columnTitle[0], PHPExcel_Cell_DataType::TYPE_STRING);
                $oC = $oac->getCellByColumnAndRow($j, $index_title)->getColumn();
                $oR = $oac->getCellByColumnAndRow($j, $index_title)->getRow();
                $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $oac->getStyle($oC.$oR)->applyFromArray($arrStyleTitle);
                $oac->getStyle($oC.$oR)->getBorders()->getAllBorders()->applyFromArray( $arrborderTitleuno);
                $j++;
            }

            $fila = 7;
            $i = 0;
            $j = 1;
            $subtotal = $total = 0;
            if(is_array($data[$valor3]) && (count($data[$valor3]) > 0)) {
                $documento = '';
                foreach ($data[$valor3] as $valor) {
                    $valores = array();
                    $noRepetir = ($documento != $valor['documento']) ? true : false;
                    $documento = $valor['documento'];
                    if ($noRepetir)
                        $i++;
                    $valores[] = array(($noRepetir) ? $i : $vacio,'STR', 'C');
                    $valores[] = array($valor['agencia'] = ($noRepetir) ? $valor['agencia'] : $vacio,'STR', ($noRepetir) ? 'L' : 'C');
                    $valores[] = array($valor['cedcli'] = ($noRepetir) ? $valor['cedcli'] : $vacio,'STR','C');
                    $valores[] = array($valor['codclisap'] = ($noRepetir) ? $valor['codclisap'] : $vacio,'STR','C');
                    $valores[] = array($valor['razcli'] = ($noRepetir) ? $this->Interfaz->limpiar_campo($valor['razcli']) : $vacio,'STR', ($noRepetir) ? 'L' : 'C');
                    $valores[] = array($valor['documento'] = ($noRepetir) ? $valor['documento'] : $vacio,'STR', ($noRepetir) ? 'L' : 'C');
                    $valores[] = array($valor['estatus'] = ($noRepetir) ? $valor['estatus'] : $vacio,'STR', ($noRepetir) ? 'L' : 'C');
                    $valores[] = array($valor['tipo'] = ($noRepetir) ? $valor['tipo'] : $vacio,'STR', ($noRepetir) ? 'L' : 'C');
                    $valores[] = array($valor['fecemi'] = ($noRepetir) ? $this->Interfaz->fecha($valor['fecemi']) : $vacio,'STR','C');
                    $valores[] = array($valor['codart'],'STR','L');
                    $valores[] = array($valor['denart'],'STR','L');
                    $valores[] = array($valor['canpro'],'N','C');
                    $valores[] = array($valor['prepro'],'N','R');
                    $subtotal = ($valor['canpro'] * $valor['prepro']);
                    $valores[] = array($subtotal,'N','R');
                    $total += $subtotal;

                    $columna = 0;
                    foreach($valores as $valor) {
                        if($valor[1] == 'STR')
                            $oac->getCellByColumnAndRow($columna,$fila)->setValueExplicit($valor[0], PHPExcel_Cell_DataType::TYPE_STRING);
                        else
                            $oac->getCellByColumnAndRow($columna,$fila)->setValueExplicit($valor[0], PHPExcel_Cell_DataType::TYPE_NUMERIC);

                        $oC = $oac->getCellByColumnAndRow($columna, $fila)->getColumn();
                        $oR = $oac->getCellByColumnAndRow($columna, $fila)->getRow();

                        if($valor[2] == 'L')
                            $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                        else if($valor[2] == 'C')
                            $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        else
                            $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

                        if($i%2 == 0)
                            $oac->getStyle($oC.$oR)->applyFromArray($arrStyleTitleuno);

                            $oac->getStyle($oC.$oR)->getBorders()->getAllBorders()->applyFromArray($arrborderTitleuno);
                            $columna++;					
                    }
                    $fila++;
                    $j++;
                }
                $totales = array();
                $totales[] = array('Total:', 'STR', 'C');
                $totales[] = array($total, 'N', 'R');

                $columna = 12;
                foreach($totales as $total) {
                    if($total[1] == 'STR')
                        $oac->getCellByColumnAndRow($columna,$fila)->setValueExplicit($total[0], PHPExcel_Cell_DataType::TYPE_STRING);
                    else {
                        $oac->getCellByColumnAndRow($columna,$fila)->setValueExplicit($total[0], PHPExcel_Cell_DataType::TYPE_NUMERIC);
                        $oac->getStyle($oC.$oR)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
                    }
                    $oC = $oac->getCellByColumnAndRow($columna, $fila)->getColumn();
                    $oR = $oac->getCellByColumnAndRow($columna, $fila)->getRow();

                    if($total[2] == 'L')
                        $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                    else if($total[2] == 'C')
                        $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    else
                        $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

                    $oac->getStyle($oC.$oR)->applyFromArray($arrStyleTotales);
                    $oac->getStyle($oC.$oR)->getBorders()->getAllBorders()->applyFromArray($arrborderTitleuno);
                    $columna++;	
                }
            }
            $k++;
        }
        $this->PhpExcel->xls->setActiveSheetIndex(0);
    }
    $this->PhpExcel->mostrar($nombre,$ext);